﻿Imports System.IO
Public Class frmHOSxPPerson
    Dim mysql As New ClsSQLhelper
    Dim Strsql As String
    Dim On_load As Boolean = True
#Region "Function"
    Sub Set_Grid(ByVal Strsql As String)
        Try
            Dim DT_Patient As New DataTable
            DT_Patient = mysql.GetMYSQLDataTable(Strsql, "patient", True)
            With DataView
                Me.Cursor = Cursors.WaitCursor
                .RowHeadersVisible = False
                .AllowDrop = False
                .AllowUserToAddRows = False
                .AllowUserToDeleteRows = False
                .AllowUserToOrderColumns = True
                .AllowUserToResizeColumns = True
                .AllowUserToResizeRows = False
                .MultiSelect = False
                '  .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells
                .AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells
                .DataSource = DT_Patient
                lblRowscount.Text = .RowCount & " Row(s)"
                Me.Cursor = Cursors.Default
            End With

            DT_Patient.Dispose()
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MsgBox(ex.Message)
        End Try
    End Sub
#End Region
    Private Sub BtnInvalidBirthDay_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnInvalidBirthDay.Click

        Strsql = "Select patient_hn,person.* from person where birthdate = '' or  birthdate is null Order by patient_hn desc  limit " & Me.NumericUpDown1.Value
        Set_Grid(Strsql)

    End Sub

    'Private Sub BtnCIDDup_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnCIDDup.Click
    '    Try
    '        Strsql = "select distinct p.cid, p.hn,CONCAT(p.pname,p.fname,' ',p.lname) as name,p.sex,"
    '        Strsql = Strsql & " p.birthday,p.last_visit,p.fathername,p.mathername,p.hometel,if(p.nationality = ' ','00',p.nationality) as nationality,"
    '        Strsql = Strsql & " if(p.birthday=p2.birthday,1,0) as birthdatematch,"
    '        Strsql = Strsql & " if(p.tmbpart=p2.tmbpart,1,0) as Tmbmatch,"
    '        Strsql = Strsql & " if(p.amppart=p2.amppart,1,0) as Ampdatematch,"
    '        Strsql = Strsql & " if(p.chwpart=p2.chwpart,1,0) as Chwdatematch,"
    '        Strsql = Strsql & " if(p.sex=p2.sex,1,0) as Sexmatch,"
    '        Strsql = Strsql & " if(p.lname=p2.lname,1,0) as lnamematch,"
    '        Strsql = Strsql & " if(p.fname=p2.fname,1,0) as fnamematch,"
    '        Strsql = Strsql & " if(p.nationality=p2.nationality,1,0) as nationalitymatch"
    '        Strsql = Strsql & " from patient p, patient p2"
    '        Strsql = Strsql & " where(p.cid = p2.cid)"
    '        Strsql = Strsql & " and   p.hn <> p2.hn"
    '        'Strsql = Strsql & " and p. CID in ('','','','1670500002587','1670500009042','1679000006727','1679000028330','1679000029174','1679000048284','1679000050599','1679000051897','1679000052249','1679000054691','1679000054926','1679000057003','1679000059081','1679000059367','1679000059481','1679000060195','1679000060641','1679000062007','1679000062201','1679000062813','3361000069402','3401000041078','3670500001943','3670501111025','4670800002021','5430600001340','5670500001087')"
    '        Strsql = Strsql & " and length(p.cid)=13"
    '        Strsql = Strsql & " order by p.cid,fnamematch,lnamematch,birthdatematch,Sexmatch,Tmbmatch,Ampdatematch,Chwdatematch,p.nationality, p.hn"
    '        ' If InStr(Strsql, " limit ", CompareMethod.Text) = 0 Then 
    '        Strsql = Strsql & " limit " & Me.NumericUpDown1.Value
    '        'Strsql = "Select * from patient where birthday = ''  Order by cid,hn  limit " & Me.NumericUpDown1.Value
    '        Dim DT_Patient As New DataTable
    '        DT_Patient = mysql.GetMYSQLDataTable(Strsql, "patient", True)
    '        With DataView

    '            .RowHeadersVisible = False
    '            .AllowDrop = False
    '            .AllowUserToAddRows = False
    '            .AllowUserToDeleteRows = False
    '            .AllowUserToOrderColumns = True
    '            .AllowUserToResizeColumns = True
    '            .AllowUserToResizeRows = False
    '            .MultiSelect = False
    '            .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells
    '            .AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells
    '            .DataSource = DT_Patient
    '        End With
    '        DT_Patient.Dispose()
    '    Catch ex As Exception
    '        MsgBox(ex.Message)
    '    End Try
    'End Sub

    Private Sub BtnNOPID_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnNOPID.Click

        Strsql = "select  vn_stat.hn,patient.cid,patient.pname,patient.fname,patient.lname as patientname"
        Strsql = Strsql & " ,vn_stat.pdx,income,rcpt_money, vn_stat.vstdate, pttypeno "
        Strsql = Strsql & " from vn_stat left join patient on vn_stat.hn = patient.hn"
        Strsql = Strsql & " where vn_stat.ym= '" & (Val(Me.TxtYear.Text) - 543) & "-" & Me.TxtMonth.Text & "'  and (select count(person_id) as cc from person where patient_hn =vn_stat.hn) < 1    group by vn_stat.hn      order by cid,fname         limit " & Me.NumericUpDown1.Value
        Set_Grid(Strsql)

    End Sub

    Private Sub BtnExportExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnExportExcel.Click

        If Me.DataView.RowCount = 0 Then Exit Sub
        If MsgBox("คุณต้องการส่งออกข้อมูลในตาราง ออกเป็นรูปแบบ Excel หรือไม่", MsgBoxStyle.Question + MsgBoxStyle.YesNo, "Export to Excel") = MsgBoxResult.No Then Exit Sub


        With SaveFileDialog1
            .FileName = "Export.xls"
            .Filter = "Excel 2003 | *.xls"
            If .ShowDialog() <> DialogResult.OK Then Exit Sub

            Dim Str As String = "" ' = "PCUCODE,PID,HN,CID,NAME,SEX,BIRTH,MSTATUS,OCCUPA,NATION,TYPEAREA,CODE_ID"
            For i = 0 To DataView.ColumnCount - 1
                Str = Str & DataView.Columns(i).HeaderText
                If i <> DataView.ColumnCount - 1 Then
                    Str = Str & ","
                End If

            Next
            Using sw As StreamWriter = New StreamWriter(.FileName, False, System.Text.Encoding.UTF8)
                sw.WriteLine(Str)

                '  
                For i As Integer = 0 To DataView.RowCount - 1
                    Str = ""
                    For j As Integer = 0 To DataView.ColumnCount - 1
                        Str = Str & DataView.Rows(i).Cells(j).Value.ToString
                        If j <> DataView.ColumnCount - 1 Then
                            Str = Str & ","
                        End If

                    Next
                    sw.WriteLine(Str)
                Next
                sw.Close()

            End Using


            MsgBox("ส่งออกข้อมูลเรียบร้อยแล้ว", MsgBoxStyle.Information)

        End With
    End Sub

    Private Sub DataView_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataView.CellContentClick

    End Sub

    Private Sub frmHOSxPPerson_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Try
            Dim DT_m_files_upload As DataTable
            Strsql = "select ROWID,FILENAME from m_files_upload "
            Strsql = Strsql & " where TYPE_DESC = 'OP Package' or  TYPE_DESC = '18 แฟ้ม' "
            Strsql = Strsql & " order by Date_SEND DESC"
            With Me.CboImportOPFile

                DT_m_files_upload = mysql.GetMYSQLDataTable(Strsql, "m_files_upload")
                If DT_m_files_upload.Rows.Count = 0 Then
                    MsgBox("คุณต้องนำข้อมูลเข้าโปรแกรมตรวจสอบ NHSO OPPP 2554 ก่อน", MsgBoxStyle.Exclamation)
                    Exit Sub
                End If
                .DataSource = DT_m_files_upload
                .DisplayMember = "FILENAME"
                .ValueMember = "ROWID"
                Me.CboImportOPFile.SelectionStart = DT_m_files_upload.Rows(0).Item("ROWID").ToString

            End With
            If InStr(Me.CboImportOPFile.Text, "FOP", CompareMethod.Text) > 0 Then
                Me.TxtYear.Text = Mid(Me.CboImportOPFile.Text, 11, 4)
                Me.TxtMonth.Text = Mid(Me.CboImportOPFile.Text, 15, 2)
            End If
            If InStr(Me.CboImportOPFile.Text, "E_PCU", CompareMethod.Text) > 0 Then
                'E_PCU_11266_201010_20101202.zip
                Me.TxtYear.Text = Val(Mid(Me.CboImportOPFile.Text, 13, 4)) + 543
                Me.TxtMonth.Text = Mid(Me.CboImportOPFile.Text, 17, 2)
            End If
            On_load = False
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
    Private Sub CboImportOPFile_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CboImportOPFile.SelectedIndexChanged
        Try
            If On_load = True Then Exit Sub
            If InStr(Me.CboImportOPFile.Text, "FOP", CompareMethod.Text) > 0 Then
                Me.TxtYear.Text = Mid(Me.CboImportOPFile.Text, 11, 4)
                Me.TxtMonth.Text = Mid(Me.CboImportOPFile.Text, 15, 2)
            End If
            If InStr(Me.CboImportOPFile.Text, "E_PCU", CompareMethod.Text) > 0 Then
                'E_PCU_11266_201010_20101202.zip
                Me.TxtYear.Text = Val(Mid(Me.CboImportOPFile.Text, 13, 4)) + 543
                Me.TxtMonth.Text = Mid(Me.CboImportOPFile.Text, 17, 2)
            End If



        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub BtnNoDiag_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnNoDiag.Click
        Strsql = "select vn_stat.vn, vn_stat.hn,patient.cid,patient.pname,patient.fname,patient.lname as patientname,income,rcpt_money, vn_stat.vstdate,vn_stat.pdx,vn_stat.dx0,vn_stat.dx1,vn_stat.dx3,vn_stat.dx4,vn_stat.dx5,pttypeno"
        Strsql = Strsql & "  from vn_stat left join patient  on vn_stat.hn = patient.hn "
        Strsql = Strsql & " where vn_stat.ym= '" & (Val(Me.TxtYear.Text) - 543) & "-" & Me.TxtMonth.Text & "' and pdx = ''    order by vn_stat.vn       limit " & Me.NumericUpDown1.Value
        Set_Grid(Strsql)
    End Sub
End Class